import re
import sys
import openpyxl
from openpyxl.utils import get_column_letter
import os.path
import glob
import os
from PySide6.QtGui import *
from PySide6.QtWidgets import *
from PySide6.QtCore import *

error_file = os.path.join(os.getcwd(), "meger_log.txt")

def app_path():
    if hasattr(sys, 'frozen'):  
        return os.path.abspath(sys.executable)
    return os.path.abspath(__file__) 

class OpenFolderWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.edit = QLineEdit()
        self.button_open = QPushButton('open excel')
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.edit)
        self.layout.addWidget(self.button_open)
        self.edit.setReadOnly(True)

class OpenExcelWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.edit = QLineEdit()
        self.button_open = QPushButton('open excel')
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.edit)
        self.layout.addWidget(self.button_open)
        self.edit.setReadOnly(True)

class ProgressBarWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.button_start = QPushButton('start')
        self.progress_bar = QProgressBar()
        self.progress_bar.setValue(0)
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.progress_bar)
        self.layout.addWidget(self.button_start)

class CodeCheckView(QWidget):
    signal_folder = Signal(str)
    signal_excelfile = Signal(str)
    signal_currentfile = Signal(str)
    def __init__(self):
        super().__init__()
        self.gtr_workbook_events = None
        self.code_path = OpenFolderWidget('source file path:   ')
        self.excel_path = OpenExcelWidget('target file path:    ')
        self.progress = ProgressBarWidget('Progress bar:')
        input_layout = QVBoxLayout()
        input_layout.addWidget(self.code_path)
        input_layout.addWidget(self.excel_path)
        input_layout.addWidget(self.progress)
        group_box_input = QGroupBox('')
        group_box_input.setLayout(input_layout)
        tab_layout = QVBoxLayout()
        tab_layout.addWidget(group_box_input)
        self.setLayout(tab_layout)
        self.setWindowTitle("Excel meger")
        self.resize(600, 300)
        self.show()

        self.code_path.button_open.clicked.connect(self.SlotChooseFolder)
        self.excel_path.button_open.clicked.connect(self.SlotChooseExcelFile)
        self.progress.button_start.clicked.connect(self.TraversalFolder)

    def ProgressBar(self, check_process): 
        self.progress.progress_bar.setValue(check_process)
        QCoreApplication.processEvents()

    def SlotChooseFolder(self): # open folder
        folder_path = QFileDialog.getOpenFileName(self)
        self.code_path.edit.setText(folder_path[0])
        self.signal_folder = folder_path[0]
    
    def SlotChooseExcelFile(self): # open excel
        file_path = QFileDialog.getOpenFileName(self)
        self.excel_path.edit.setText(file_path[0])
        self.signal_excelfile = file_path[0]

    def TraversalFolder(self): # start
        folder_path = self.signal_folder
        excel_path = self.signal_excelfile
        result = main(folder_path, excel_path)
        self.ProgressBar(100)
        if result == 0:
            QMessageBox.information(None, 'result', f'Excel file meger SUCC end.           ')


def main(source_file, target_file):
    error_list = []
    update_dict = {}
    start_flag = False
    try:
        # 加载工作簿
        wb = openpyxl.load_workbook(source_file, data_only=True)
        # 获取指定的sheet
        sheet = wb.worksheets[0]
        for row in sheet.iter_rows():
            for cell in row:
                if start_flag and cell.font and cell.font.color and cell.font.color.rgb == "FFFF0000":
                    update_dict[cell.coordinate] = str(cell.value).strip()
                else:
                    if str(cell.value).strip() == "Jira":
                        start_flag = True
                        break
        # print(update_dict)
    except Exception as e:
        QMessageBox.information(None, 'warning', f'读取[{source_file}]文件失败[{e}].           ')
        return 1

    if update_dict:
        try:
            # 加载工作簿
            wb = openpyxl.load_workbook(target_file, keep_vba=True)
            # 获取指定的sheet
            sheet = wb.worksheets[0]
            for key,value in update_dict.items():
                tmp_value = sheet[key].value
                if str(tmp_value).strip() == value:
                    error_list.append(f"单元格[{key}] 源数据[{tmp_value}] 新数据[{value}] 数据一致, 不需要更新, 请确认源数据是否正确  !!!")
                else:
                    sheet[key] = value
                    # sheet.range(key).value = value
                    error_list.append(f"单元格[{key}] 源数据[{tmp_value}] 更新为新数据[{value}]")
            wb.save(target_file)
            wb.close()
        except Exception as e:
            QMessageBox.information(None, 'warning', f'更新[{target_file}]文件失败[{e}].           ')
            return 1
    else:
        QMessageBox.information(None, 'warning', f'源文件未检测到更新数据, 请确认文件是否正确 !!!.           ')
        return 1

    with open(error_file, 'a', encoding='utf-8') as f:
        for error in error_list:
            f.write(str(error) + '\n')
    return 0


if __name__ == "__main__":
    if os.path.exists(error_file):
        os.remove(error_file)

    app = QApplication([])
    window = CodeCheckView()
    app.exec()